package benny.ApkposStore.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.util.Log;
import benny.ApkposStore.model.OrderNew;
import benny.ApkposStore.utils.ApkConstant;
import benny.ApkposStore.utils.ApkUntils;

public class OrderTableNew {
	/* table name */
	public static final String TABLENAME = "apkpos_orders_new";
	public static final String TPrintNAME = "apkpos_orders_print";

	/* column names */
	static final public String CID = "_id"; // AUTO generate
	static final public String CTS = "cts"; // timestamp

	static final public String CORDERID = "orderid"; // order_id
	static final public String CTABLE_NUM = "table_num"; // room_alias_id
	static final public String CTABLE_EXT = "table_extent"; // room_mark
	static final public String CPEOPLE_NUM = "people_num"; // person_count
	static final public String CDISH_TOTAL = "dish_total"; // amount
	public static final String CROOM_STATE = "room_state"; // room_state
	public static final String COPEN_TIME = "open_time"; // open_time
	public static final String CCLOSE_TIME = "close_time"; // close_time
	public static final String CORDER_PAY = "order_pay"; // final_amount
	public static final String CDIC_RATE = "dic_rate"; // discount_rate
	public static final String CDIC_CHARGE = "dic_charge"; // discount_amount
	public static final String CFREE_CHARGE = "free_charge";// free_amount
	public static final String CSVC_RATE = "svc_rate"; // service_rate
	public static final String CSVC_CHARGE = "svc_charge"; // service_amount
	public static final String CEXTRA_TOTAL = "extra_total"; // extra_amount
	public static final String COPENUSER = "openuser"; // opener
	public static final String CCASHUSER = "cashuser"; // casher
	public static final String ORDER_STATE = "order_state"; // room_state
	public static final String ORDERINT = "orderint";

	private Context mCtx;

	public static final String createTableSQL = "create table if not exists "
			+ TABLENAME + "(" + CID + " integer primary key autoincrement,"
			+ CTS + " int not null default 0," + CORDERID
			+ " varchar(40) not null default 0," + ORDERINT + " int ,"
			+ CTABLE_NUM + " int not null default 0," + CTABLE_EXT
			+ " varchar(2) ," + CPEOPLE_NUM + " int not null default 0,"
			+ CDISH_TOTAL + " numeric(18,2) not null default 0," + CROOM_STATE
			+ " varchar(2)," + COPEN_TIME + " DATETIME ," + CCLOSE_TIME
			+ " DATETIME ," + CORDER_PAY + " numeric(18,2)," + CDIC_RATE
			+ " int ," + CDIC_CHARGE + " numeric(18,2) ," + CFREE_CHARGE
			+ " numeric(18,2)," + CSVC_CHARGE + " numeric(18,2) ," + CSVC_RATE
			+ " int ," + CEXTRA_TOTAL + " numeric(18,2)," + COPENUSER
			+ "  varchar(15)," + CCASHUSER + "  varchar(15)," + ORDER_STATE
			+ "  varchar(2))";

	public static final String createPrintSQL = "create table if not exists "
			+ TPrintNAME + "(" + CID + " integer primary key autoincrement,"
			+ CTS + " int not null default 0," + CORDERID
			+ " varchar(40) not null default 0," + ORDERINT + " int ,"
			+ CTABLE_NUM + " int not null default 0," + CTABLE_EXT
			+ " varchar(2) ," + CPEOPLE_NUM + " int not null default 0,"
			+ CDISH_TOTAL + " numeric(18,2) not null default 0," + CROOM_STATE
			+ " varchar(2)," + COPEN_TIME + " DATETIME ," + CCLOSE_TIME
			+ " DATETIME ," + CORDER_PAY + " numeric(18,2)," + CDIC_RATE
			+ " int ," + CDIC_CHARGE + " numeric(18,2) ," + CFREE_CHARGE
			+ " numeric(18,2)," + CSVC_CHARGE + " numeric(18,2) ," + CSVC_RATE
			+ " int ," + CEXTRA_TOTAL + " numeric(18,2)," + COPENUSER
			+ "  varchar(15)," + CCASHUSER + "  varchar(15)," + ORDER_STATE
			+ "  varchar(2))";

	private static String tag = "apkpos_orders_new";

	public OrderTableNew(Context ctx) {
		mCtx = ctx;
	}

	/**
	 * insert a row
	 * 
	 * @param bean
	 *            :
	 * @param context
	 *            :Application's context
	 * @return the row ID of the newly inserted row, or -1 if an error occurred
	 */
	public long insert(OrderNew bean) {

		ContentValues values = beanToContentValues(bean);
		values.remove(CID);
		synchronized (ApkConstant.SQLLOCK) {
			try {
				SQLiteDatabase db = DBHelper.getWritableDatabase(mCtx);
				long rowId = db.insertOrThrow(TABLENAME, null, values);
				Log.d(tag, "insert a row success");
				return rowId;
			} catch (SQLiteException e) {
				Log.e(tag, "insert " + TABLENAME + " error:" + e.getMessage());
			}
		}
		return -1;
	}

	/**
	 * get a table cursor
	 * 
	 * @param ctx
	 *            :Application's context
	 * @return cursor
	 */
	public Cursor getCurSor(String selection, String selectionArgs[]) {
		String columns[] = { CID, CTS, CORDERID, CTABLE_NUM, CTABLE_EXT,
				CPEOPLE_NUM, CDISH_TOTAL, CROOM_STATE, COPEN_TIME, CCLOSE_TIME,
				CORDER_PAY, CDIC_RATE, CDIC_CHARGE, CFREE_CHARGE, CSVC_RATE,
				CSVC_CHARGE, CEXTRA_TOTAL, COPENUSER, CCASHUSER, ORDER_STATE };
		// String selection = CORDERID +"= ?";
		// String selectionArgs[] = {"100"};
		String groupBy = "";
		String having = "";
		String orderBy = "";
		String limit = "";

		Cursor cursor = null;
		try {
			SQLiteDatabase db = DBHelper.getReadableDatabase(mCtx);
			cursor = db.query(TABLENAME, columns, selection, selectionArgs,
					groupBy, having, orderBy, limit);
			return cursor;
		} catch (SQLiteException e) {
			Log.e(tag, "query failed:" + e.getMessage());
		}
		return null;
	}

	/**
	 * update a service record
	 * 
	 * @param bean
	 */
	public boolean update(OrderNew bean) {
		synchronized (ApkConstant.SQLLOCK) {
			try {
				String whereClause = " _id= " + bean.getId();
				SQLiteDatabase db = DBHelper.getWritableDatabase(mCtx);
				ContentValues values = beanToContentValues(bean);
				db.update(TABLENAME, values, whereClause, null);
				Log.d(tag, "update a row success");
			} catch (SQLiteException e) {
				Log.e(tag, "update " + TABLENAME + " error:" + e.getMessage());
				return false;
			}
		}
		return true;
	}

	/**
	 * delete a row
	 * 
	 * @param rowId
	 * @param ctx
	 * @return
	 */
	public boolean delete(int rowId) {
		try {
			String sql = "delete from " + TABLENAME + " where _id = " + rowId;
			ApkUntils.execSQL(mCtx, sql);
			Log.d(tag, "delete a row success");
		} catch (SQLiteException e) {
			Log.e(tag, "delete failed:" + e.getMessage());
			return false;
		}
		return true;
	}

	/**
	 * 按订单号删除
	 * 
	 * @param orderid
	 * @return
	 */
	public boolean deleteByOrderid(String orderid) {
		try {

			String sql = "delete from " + TABLENAME + " where " + CORDERID
					+ " = " + orderid;
			ApkUntils.execSQL(mCtx, sql);
			Log.d(tag, "delete a row success");
		} catch (SQLiteException e) {
			Log.e(tag, "delete failed:" + e.getMessage());
			return false;
		}
		return true;
	}

	/**
	 * convert db cursor to bean structure
	 * 
	 * @param cursor
	 * @return Order
	 */
	public OrderNew CurSorToGasBean(Cursor cursor) {
		OrderNew bean = new OrderNew();
		bean.setId(cursor.getInt(cursor.getColumnIndex(CID)));
		bean.setTs(cursor.getInt(cursor.getColumnIndex(CTS)));
		bean.setOrderid(cursor.getString(cursor.getColumnIndex(CORDERID)));
		bean.setTable_num(cursor.getInt(cursor.getColumnIndex(CTABLE_NUM)));
		bean.setTable_extent(cursor.getString(cursor.getColumnIndex(CTABLE_EXT)));
		bean.setPeople_num(cursor.getInt(cursor.getColumnIndex(CPEOPLE_NUM)));
		bean.setDish_total(cursor.getInt(cursor.getColumnIndex(CDISH_TOTAL)));
		bean.setRoom_state(cursor.getInt(cursor.getColumnIndex(CROOM_STATE)));
		bean.setOpen_time(cursor.getString(cursor.getColumnIndex(COPEN_TIME)));
		bean.setClose_time(cursor.getString(cursor.getColumnIndex(CCLOSE_TIME)));
		bean.setOrder_pay(cursor.getInt(cursor.getColumnIndex(CORDER_PAY)));
		bean.setDic_rate(cursor.getInt(cursor.getColumnIndex(CDIC_RATE)));
		bean.setDic_charge(cursor.getInt(cursor.getColumnIndex(CDIC_CHARGE)));
		bean.setFree_charge(cursor.getInt(cursor.getColumnIndex(CFREE_CHARGE)));
		bean.setSvc_rate(cursor.getInt(cursor.getColumnIndex(CSVC_RATE)));
		bean.setExtra_total(cursor.getInt(cursor.getColumnIndex(CEXTRA_TOTAL)));
		bean.setOpenuser(cursor.getString(cursor.getColumnIndex(COPENUSER)));
		bean.setCashuser(cursor.getString(cursor.getColumnIndex(CCASHUSER)));
		bean.setOrder_state(cursor.getString(cursor.getColumnIndex(ORDER_STATE)));

		return bean;
	}

	private ContentValues beanToContentValues(OrderNew bean) {
		ContentValues values = new ContentValues();

		values.put(CID, bean.getId());
		values.put(CTS, bean.getTs());
		values.put(CORDERID, bean.getOrderid());
		values.put(CTABLE_NUM, bean.getTable_num());
		values.put(CTABLE_EXT, bean.getTable_extent());
		values.put(CPEOPLE_NUM, bean.getPeople_num());
		values.put(CDISH_TOTAL, bean.getDish_total());
		values.put(CROOM_STATE, bean.getRoom_state());
		values.put(COPEN_TIME, bean.getOpen_time());
		values.put(CCLOSE_TIME, bean.getClose_time());
		values.put(CORDER_PAY, bean.getOrder_pay());
		values.put(CDIC_RATE, bean.getDic_rate());
		values.put(CDIC_CHARGE, bean.getDic_charge());
		values.put(CFREE_CHARGE, bean.getFree_charge());
		values.put(CSVC_RATE, bean.getSvc_rate());
		values.put(CEXTRA_TOTAL, bean.getExtra_total());
		values.put(CCASHUSER, bean.getCashuser());
		values.put(COPENUSER, bean.getOpenuser());
		values.put(ORDER_STATE, bean.getOrder_state());

		return values;
	}

}
